package com.training1.util;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;

import com.training1.core.common.exception.ExcelException;
import com.training1.core.common.utils.DateUtils;
import com.training1.core.common.utils.FileUtils;
import com.training1.core.common.utils.StringUtils;
import com.training1.core.modules.dao.MemberDao;

public class ImportExcel {

	private static final Logger log = LoggerFactory.getLogger(ImportExcel.class);
	
	
	/**
	 * 
	 * @param in excel文件流
	 * @param dao 检查手机号的dao
	 * @param filePath 文件保存路径，包括完整文件名 如D://upload/201602.xlsx
	 * @return
	 * @throws ExcelException
	 */
	public static Map<String, String> validate(InputStream in, MemberDao dao,String filePath) throws ExcelException{
		
		Assert.hasText(filePath, "文件路径不能为空");
		Workbook wb;
		File source;
		try {
			//保存为临时文件
			source = new File(UUID.randomUUID()+"temp.xlsx");
			FileUtils.copyInputStreamToFile(in, source);
			//读取文件
			 wb = getwb(source);
		} catch (IOException e) {
			log.error("未找到excel",e);
			throw new ExcelException("未找到excel");
		}
		//设置一个全局错误
		ExcelException err = new ExcelException();
		//取得sheet
		Sheet sheet = wb.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		
		// 记录总共多少笔
		Integer count = 0;
		// 记录总共充值金额
		BigDecimal total = new BigDecimal(0.00);
		for(int i=1 ; i <= lastRowNum ; i++){
			//循环读取每行,从第2行开始
			try{
				Row row = sheet.getRow(i);
				if(row == null)continue;
				//得到单元格的值
				String cellphone = getCellValue(row.getCell(0));
				String memberName = getCellValue(row.getCell(1));
				String month = getCellValue(row.getCell(2));
				String cash = getCellValue(row.getCell(3));
				//验证手机
				int r = validatePhone(cellphone,memberName, dao);
				if(r==-1){
					log.debug("导入数据错误1,第{}行", i+1);
					// throw new ExcelException();
					err.setErrMsg("Excel行"+(i+1)+"的会员手机号不正确     ");
				}else if(r==-2){
					log.debug("导入数据错误2,第{}行,手机号重复", i+1);
					err.setErrMsg("Excel行"+(i+1)+"的会员不存在或被锁定     ");
				}
				if(!validateMonth(month)){
					log.debug("导入数据错误2,第{}行", i+1);
					//throw new ExcelException();
					err.setErrMsg("Excel行"+(i+1)+"的年月不正确     ");
				}
				if(!validateCash(cash)){
					log.debug("导入数据错误2,第{}行", i+1);
					//throw new ExcelException();
					err.setErrMsg("Excel行"+(i+1)+"的金额不正确(金额应为8位整数2位小数以内的正数)     ");
				}
				count++;
				total=total.add(new BigDecimal(cash));
			}catch(Exception e){
				err.setErrMsg("Excel行"+(i+1)+"的数据有错误     ");
				log.debug("导入数据错误", e);
			}
		}
		if(err.isHasErr()){
			//删除临时文件
			source.deleteOnExit();
			throw err;
		}
		try {
			new File(filePath).deleteOnExit();
			FileUtils.copyFile(source, new File(filePath));
		} catch (IOException e) {
			log.error("文件保存错误", e);
			throw new ExcelException("excel保存错误");
		}finally{
			source.deleteOnExit();
		}
		Map<String, String> map = new HashMap<String, String>();
		map.put("errMsg", "上传成功,合计"+count+"笔,共"+total+"元");
		return map;
	}
	
	/**
	 * 
	 * @param in excel文件流
	 * @param dao 检查手机号的dao
	 * @param filePath 文件保存路径，包括完整文件名 如D://upload/201602.xlsx
	 * @return
	 * @throws ExcelException
	 */
	public static Map<String, String> validate1(InputStream in, MemberDao dao,String filePath) throws ExcelException{
		
		Assert.hasText(filePath, "文件路径不能为空");
		Workbook wb;
		File source;
		try {
			//保存为临时文件
			source = new File(UUID.randomUUID()+"temp.xlsx");
			FileUtils.copyInputStreamToFile(in, source);
			//读取文件
			 wb = getwb(source);
		} catch (IOException e) {
			log.error("未找到excel",e);
			throw new ExcelException("未找到excel");
		}
		//设置一个全局错误
		ExcelException err = new ExcelException();
		//取得sheet
		Sheet sheet = wb.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		
		// 记录总共多少条
		Integer count = 0;
		// 记录总共充值金额
		for(int i=1 ; i <= lastRowNum ; i++){
			//循环读取每行,从第2行开始
			try{
				Row row = sheet.getRow(i);
				if(row == null)continue;
				//得到单元格的值
				String memberName = getCellValue(row.getCell(0));
				String cellphone = getCellValue(row.getCell(1));
				String cash = getCellValue(row.getCell(2));
				String month = getCellValue(row.getCell(3));
				//验证手机
				int r = validatePhone1(cellphone,memberName, dao);
				if(r==-1){
					log.debug("导入数据错误1,第{}行", i+1);
					// throw new ExcelException();
					err.setErrMsg("Excel行"+(i+1)+"的会员手机号不正确     ");
				}else if(r==-2){
					log.debug("导入数据错误2,第{}行,手机号重复", i+1);
					err.setErrMsg("Excel行"+(i+1)+"的会员姓名不能为空    ");
				}
				if(!validateMonth1(month)){
					log.debug("导入数据错误2,第{}行", i+1);
					//throw new ExcelException();
					err.setErrMsg("Excel行"+(i+1)+"的年月日不正确     ");
				}
				if(!validateCash(cash)){
					log.debug("导入数据错误2,第{}行", i+1);
					//throw new ExcelException();
					err.setErrMsg("Excel行"+(i+1)+"的金额不正确(金额应为8位整数2位小数以内的正数)     ");
				}
				count++;
			}catch(Exception e){
				err.setErrMsg("Excel行"+(i+1)+"的数据有错误     ");
				log.debug("导入数据错误", e);
			}
		}
		if(err.isHasErr()){
			//删除临时文件
			source.deleteOnExit();
			throw err;
		}
		try {
			new File(filePath).deleteOnExit();
			FileUtils.copyFile(source, new File(filePath));
		} catch (IOException e) {
			log.error("文件保存错误", e);
			throw new ExcelException("excel保存错误");
		}finally{
			source.deleteOnExit();
		}
		Map<String, String> map = new HashMap<String, String>();
		map.put("errMsg", "上传成功,合计"+count+"条");
		return map;
	}
	
	/**
	 * 
	 * @param in excel文件流
	 * @param dao 检查手机号的dao
	 * @param filePath 文件保存路径，包括完整文件名 如D://upload/201602.xlsx
	 * @return
	 * @throws ExcelException
	 */
	public static Map<String, String> validate2(InputStream in, MemberDao dao,String filePath) throws ExcelException{
		
		Assert.hasText(filePath, "文件路径不能为空");
		Workbook wb;
		File source;
		try {
			//保存为临时文件
			source = new File(UUID.randomUUID()+"temp.xlsx");
			FileUtils.copyInputStreamToFile(in, source);
			//读取文件
			 wb = getwb(source);
		} catch (IOException e) {
			log.error("未找到excel",e);
			throw new ExcelException("未找到excel");
		}
		//设置一个全局错误
		ExcelException err = new ExcelException();
		// 记录第一个sheet数
		Integer count1 = 0;
		// 记录第二个sheet数
		Integer count2 = 0;
		// 记录第三个sheet数
		Integer count3 = 0;
		// 记录第四个sheet数
		Integer count4 = 0;
		// 记录第五个sheet数
		Integer count5 = 0;
		// 记录第一个sheet总金额
		BigDecimal total1 = new BigDecimal(0.00);
		// 记录第二个sheet总金额
		BigDecimal total2 = new BigDecimal(0.00);
		// 记录第三个sheet总金额
		BigDecimal total3 = new BigDecimal(0.00);
		// 记录第四个sheet总金额
		BigDecimal total4 = new BigDecimal(0.00);
		// 记录第五个sheet总金额
		BigDecimal total5 = new BigDecimal(0.00);
		//取得sheet
		for(int h=0;h<=4;h++){
			Sheet sheet = wb.getSheetAt(h);
			int lastRowNum = sheet.getLastRowNum();
			for(int i=1 ; i <= lastRowNum ; i++){
				//循环读取每行,从第2行开始
				try{
					Row row = sheet.getRow(i);
					if(row == null)continue;
					//得到单元格的值
					String cellphone = getCellValue(row.getCell(1));
					String cash = getCellValue(row.getCell(2));
					//验证手机
					int r = validatePhone2(cellphone,dao);
					if(r==-1){
						log.debug("sheet"+(h+1)+","+"导入数据错误1,第{}行", i+1);
						err.setErrMsg("sheet"+(h+1)+","+"第"+(i+1)+"行的手机号不正确     ");
						break;
					}else if(r==-2){
						log.debug("sheet"+(h+1)+","+"导入数据错误2,第{}行", i+1);
						err.setErrMsg("sheet"+(h+1)+","+"第"+(i+1)+"行的会员不存在或被锁定     ");
						break;
					}
					if(!validateCash(cash)){
						log.debug("sheet"+(h+1)+","+"导入数据错误2,第{}行", i+1);
						//throw new ExcelException();
						err.setErrMsg("sheet"+(h+1)+","+"第"+(i+1)+"行的金额不正确(金额应为8位整数2位小数以内的正数)     ");
					}
					if (h==0) {
						count1++;
						total1=total1.add(new BigDecimal(cash));
					} else if (h == 1) {
						count2++;
						total2=total2.add(new BigDecimal(cash));
					} else if (h == 2) {
						count3++;
						total3=total3.add(new BigDecimal(cash));
					} else if (h == 3) {
						count4++;
						total4=total4.add(new BigDecimal(cash));
					} else {
						count5++;
						total5=total5.add(new BigDecimal(cash));
					}
				}catch(Exception e){
					err.setErrMsg("sheet"+(h+1)+","+"第"+(i+1)+"行的数据有错误     ");
					log.debug("导入数据错误", e);
				}
			}
		}
		if(err.isHasErr()){
			//删除临时文件
			source.deleteOnExit();
			throw err;
		}
		try {
			new File(filePath).deleteOnExit();
			FileUtils.copyFile(source, new File(filePath));
		} catch (IOException e) {
			log.error("文件保存错误", e);
			throw new ExcelException("excel保存错误");
		}finally{
			source.deleteOnExit();
		}
		Map<String, String> map = new HashMap<String, String>();
		map.put("success1", "金额474为"+count1+"笔,共"+total1+"元;");
		map.put("success2", "金额2133-1为"+count2+"笔,共"+total2+"元;");
		map.put("success3", "金额2133-2为"+count3+"笔,共"+total3+"元;");
		map.put("success4", "金额600为"+count4+"笔,共"+total4+"元;");
		map.put("success5", "金额800为"+count5+"笔,共"+total5+"元!");
		return map;
	}
	
	/**
	 * 验证手机号 数字11位 在会员表中查询记录数，条件为会员手机号+黑名单='0'
	 * @param cellphone
	 * @param dao
	 * @return 1:成功,-1:数据有误,-2:会员不存在或被锁定
	 */
	private static int validatePhone2(String cellphone, MemberDao dao){
		//把excel中取出来的电话点号去掉
		cellphone = cellphone.split("\\.")[0];
		if(StringUtils.length(cellphone)!=11)return -1;
		Map<String, Object> param = new HashMap<String, Object>();
		param.put("cellPhone", cellphone);
		param.put("blackListFlag", 0);
		Integer count = dao.findMemberCount(param);
		if(count!=1){
			return -2;
		}
		return 1;
	}
	

	public static Workbook getwb(File source) throws IOException{
		Workbook wb ;
		try{
			wb = new XSSFWorkbook(FileUtils.openInputStream(source));
		}catch(Exception e){
			wb = new HSSFWorkbook(FileUtils.openInputStream(source));
		}
		return wb;
		
	}
	
	/**
	 * 得到单元格的值
	 * @param cell
	 * @return
	 */
	public static String getCellValue(Cell cell){
		if(cell==null){
			return null;
		}
		try{
			return cell.getStringCellValue();
		}catch(Exception e){}
		try{
			DecimalFormat df = new DecimalFormat("0.00");   
			double d = cell.getNumericCellValue();
			BigDecimal b = new BigDecimal(df.format(d));
			return b.stripTrailingZeros().toPlainString();
		}catch (Exception e) {}
		try{
			return DateUtils.formatDate(cell.getDateCellValue());
		}catch(Exception e){}
		try{
			return Boolean.toString(cell.getBooleanCellValue());
		}catch(Exception e){}
		return null;
	}
	
	/**
	 * 验证手机号 数字11位 在会员表中查询记录数，条件为会员手机号+黑名单='0'
	 * @param cellphone
	 * @param dao
	 * @return 1:成功,-1:数据有误,-2:会员不存在或被锁定
	 */
	private static int validatePhone(String cellphone,String memberName, MemberDao dao){
		//把excel中取出来的电话点号去掉
		cellphone = cellphone.split("\\.")[0];
		if(StringUtils.length(cellphone)!=11 || StringUtils.anyIsEmpty(memberName))return -1;
		Map<String, Object> param = new HashMap<String, Object>();
		param.put("cellPhone", cellphone);
		param.put("memberName", memberName);
		param.put("blackListFlag", 0);
		Integer count = dao.findMemberCount(param);
		if(count!=1){
			return -2;
		}
		return 1;
	}
	
	/**
	 * 验证手机号 数字11位
	 * @param cellphone
	 * @param dao
	 * @return 1:成功,-1:数据有误
	 */
	private static int validatePhone1(String cellphone,String memberName, MemberDao dao){
		//把excel中取出来的电话点号去掉
		cellphone = cellphone.split("\\.")[0];
		if(StringUtils.length(cellphone)!=11 ){
			return -1;
		}
		if(StringUtils.anyIsEmpty(memberName)){
			return -2;
		}
		return 1;
	}
	
	/**
	 * 验证月份,数字6位，且后两位在01—12之内
	 * @param month
	 * @return
	 */
	private static boolean validateMonth(String month){
		try{
			DateUtils.parseDateStrictly(month, "yyyyMM");
		}catch(Exception e){
			return false;
		}
		return true;
	}
	
	/**
	 * 验证月份,数字6位，且后两位在01—12之内
	 * @param month
	 * @return
	 */
	private static boolean validateMonth1(String month){
		try{
			DateUtils.parseDateStrictly(month, "yyyyMMdd");
		}catch(Exception e){
			return false;
		}
		return true;
	}
	
	/**
	 * 验证金额 数字(10,2)
	 * @param cash
	 * @return
	 */
	private static boolean validateCash(String cash){
		try{
			if(StringUtils.isBlank(cash))return false;
			return cash.matches("\\d{1,8}(\\.\\d{1,2})?");
		}catch(Exception e){
			return false;
		}
		
	}
	
	public static void main(String[] args) {
//		boolean b = validateMonth("201612");
//		System.out.println(b);
//		boolean cash = validateCash("1111");
//		System.out.println(cash);
		
//		File f = new File("D:/a.xlsx");
//		
//		try {
//			FileInputStream fileInputStream = FileUtils.openInputStream(f);
//			int read = fileInputStream.read();
//			System.out.println(read);
//			fileInputStream.close();
//		
//		} catch (IOException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
		
//		try {
//			validate(FileUtils.openInputStream(f), null, "D:/aa.xlsx");
//		} catch ( ExcelException  e) {
//			e.printStackTrace();
//			System.out.println(e.getErrMsg());
//		}catch (Exception e) {
//		}
		DecimalFormat df   = new DecimalFormat("0.00000");   
		double d = 111.33;
//		
//		BigDecimal bd = new BigDecimal(d, );
		System.out.println(df.format(d));
	}
	
	/**
	 * 
	 * @param in excel文件流
	 * @param dao 检查手机号的dao
	 * @param filePath 文件保存路径，包括完整文件名 如D://upload/201606.xlsx
	 * @return
	 * @throws ExcelException
	 */
	public static Map<String, String> validate3(InputStream in, MemberDao dao,String filePath) throws ExcelException{
		
		Assert.hasText(filePath, "文件路径不能为空");
		Workbook wb;
		File source;
		try {
			//保存为临时文件
			source = new File(UUID.randomUUID()+"temp.xlsx");
			FileUtils.copyInputStreamToFile(in, source);
			//读取文件
			 wb = getwb(source);
		} catch (IOException e) {
			log.error("未找到excel",e);
			throw new ExcelException("未找到excel");
		}
		//设置一个全局错误
		ExcelException err = new ExcelException();
		//取得sheet
		Sheet sheet = wb.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		
		// 会员重复判断
		for (int j = 1; j<=lastRowNum; j++) {
			Row row1 = sheet.getRow(j);
			String phoneNo = ImportExcel.getCellValue(row1.getCell(0));
			for (int k = lastRowNum; k > j;k--) {
				Row row2 = sheet.getRow(k);
				String phoneNo1 = ImportExcel.getCellValue(row2.getCell(0));
				if (phoneNo1 != null && phoneNo != null) {
					if (phoneNo.equals(phoneNo1)) {
						err.setErrMsg("Excel行"+(k+1)+"和行"+(j+1)+"的会员手机号重复，请合并");
				}}
			}
		}
		
		// 记录总共多少笔
		Integer count = 0;
		// 记录总共提现金额
		BigDecimal total = new BigDecimal(0.00);
		for(int i=1 ; i <= lastRowNum ; i++){
			//循环读取每行,从第2行开始
			try{
				Row row = sheet.getRow(i);
				if(row == null)continue;
			
				// 会员手机号
				String cellPhone = ImportExcel.getCellValue(row.getCell(0));
				// 会员姓名
				String memberName = ImportExcel.getCellValue(row.getCell(1));
				// 提现金额
				BigDecimal cash = new BigDecimal(ImportExcel.getCellValue(row.getCell(2)));			
				
				//验证手机
				
				int r = validatePhone(cellPhone,memberName, dao);
				if(r==-1){
					log.debug("导入数据错误1,第{}行", i+1);
					//throw new ExcelException();
					err.setErrMsg("Excel行"+(i+1)+"的会员手机号不正确   ");
				}else if(r==-2){
					log.debug("导入数据错误2,第{}行,手机号重复", i+1);
					err.setErrMsg("Excel行"+(i+1)+"的会员不存在或被锁定     ");
				}
				
				if(!validateCash(cash.toString())){
					log.debug("导入数据错误2,第{}行", i+1);
					//throw new ExcelException();
					err.setErrMsg("Excel行"+(i+1)+"的提现金额不正确(大于8位整数或存在数字以外的字符)   ");
				}
				// 提现负数check
				if (cash.signum() == -1) {
					log.debug("导入数据错误2,第{}行", i+1);
					//throw new ExcelException();
					err.setErrMsg("Excel行"+(i+1)+"的提现金额不能为负数   ");
				}
				
				// 提现金额和可提现比较
				BigDecimal ableCash = dao.getMemberAvaibleCash(cellPhone);
				if (ableCash == null) {
					ableCash = new BigDecimal(0);
				}
				
				if (ableCash.compareTo(cash) == -1) {
					log.debug("导入数据错误2,第{}行", i+1);
					//throw new ExcelException();
					err.setErrMsg("Excel行"+(i+1)+"的会员余额不足(提现金额不能大于余额)   ");
				}

				count++;
				total=total.add(cash);
				
			}catch(Exception e){
				err.setErrMsg("Excel行"+(i+1)+"的数据有错误     ");
				log.debug("导入数据错误", e);
			}
			
		}
		if(err.isHasErr()){
			//删除临时文件
			source.deleteOnExit();
			throw err;
		}
		try {
			new File(filePath).deleteOnExit();
			FileUtils.copyFile(source, new File(filePath));
		} catch (IOException e) {
			log.error("文件保存错误", e);
			throw new ExcelException("excel保存错误");
		}finally{
			source.deleteOnExit();
		}
		Map<String, String> map = new HashMap<String, String>();
		map.put("errMsg", "批量上传成功,合计"+count+"笔,共"+total+"元");
		return map;
	}
}
